/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mx.ipn.escuela.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import mx.ipn.escuela.modelo.Alumno;

/**
 *
 * @author jdmr
 */
public class AlumnoDao {

    public List<Alumno> lista() {
        List<Alumno> alumnos = new ArrayList();
        Connection conn = null;
        try {
            conn = obtieneConexion();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from alumnos");
            while(rs.next()) {
                Alumno alumno = new Alumno();
                alumno.setId(rs.getLong("id"));
                alumno.setMatricula(rs.getString("matricula"));
                alumno.setNombre(rs.getString("nombre"));
                alumno.setApellido(rs.getString("apellido"));
                alumno.setCorreo(rs.getString("correo"));
                alumnos.add(alumno);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try { conn.close(); } catch(Exception e){}
            }
        }
        return alumnos;
    }
    
    public Alumno obtiene(Long id) {
        Alumno alumno = null;
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("select * from alumnos where id = ?");
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                alumno = new Alumno();
                alumno.setId(rs.getLong("id"));
                alumno.setMatricula(rs.getString("matricula"));
                alumno.setNombre(rs.getString("nombre"));
                alumno.setApellido(rs.getString("apellido"));
                alumno.setCorreo(rs.getString("correo"));
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try { conn.close(); } catch(Exception e){}
            }
        }
        return alumno;
    }

    public Alumno crea(Alumno alumno) {
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("insert into " +
                    "alumnos(matricula,nombre,apellido,correo)" +
                    "values(?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, alumno.getMatricula());
            ps.setString(2, alumno.getNombre());
            ps.setString(3, alumno.getApellido());
            ps.setString(4, alumno.getCorreo());

            ps.executeUpdate();
            ResultSet rs = ps.getGeneratedKeys();
            if(rs.next()) {
                alumno.setId(rs.getLong(1));
            } else {
                throw new RuntimeException("No se pudieron obtener " +
                        "las llaves generadas");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try { conn.close(); } catch(Exception e){}
            }
        }
        return alumno;
    }

    public Alumno actualiza(Alumno alumno) {
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("update alumnos " +
                    "set nombre = ?, apellido = ?, correo = ? where id = ?");
            ps.setString(1, alumno.getNombre());
            ps.setString(2, alumno.getApellido());
            ps.setString(3, alumno.getCorreo());
            ps.setLong(4, alumno.getId());

            ps.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try { conn.close(); } catch(Exception e){}
            }
        }
        return alumno;
    }

    public void elimina(Long id) {
        Connection conn = null;
        try {
            conn = obtieneConexion();
            PreparedStatement ps =
                    conn.prepareStatement("delete from alumnos where id = ?");
            ps.setLong(1, id);

            ps.executeUpdate();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AlumnoDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conn != null) {
                try { conn.close(); } catch(Exception e){}
            }
        }
    }

    private Connection obtieneConexion() throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///escuela","ipn","ipn00");
        return conn;
    }

}
